package cn.iocoder.yudao.framework.excel.core.util.wengfu;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;

import javax.script.ScriptEngine;
import javax.script.ScriptEngineManager;
import javax.script.ScriptException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.*;
import java.util.concurrent.ConcurrentHashMap;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Component
@Slf4j
public class FormulaCalculationService {

    // 数字格式化器，保留两位小数
    private static final DecimalFormat DECIMAL_FORMAT = new DecimalFormat("0.00");

    // 支持的 Excel 函数列表
    private static final Set<String> EXCEL_FUNCTIONS = new HashSet<>(Arrays.asList(
            "IF", "ISERROR", "ISERR", "ISNA", "ISLOGICAL", "ISTEXT", "ISNUMBER",
            "SUM", "AVERAGE", "MIN", "MAX", "COUNT"
    ));

    static {
        DECIMAL_FORMAT.setRoundingMode(RoundingMode.HALF_UP);
    }

    /**
     * 批量计算公式 - 完全使用自定义计算器
     */
    public Map<String, Object> calculateAllFormulas(Workbook workbook, Map<String, String> formulaMap) {
        Map<String, Object> results = new ConcurrentHashMap<>();

        if (formulaMap == null || formulaMap.isEmpty()) {
            log.warn("公式映射为空，无需计算");
            return results;
        }

        try {
            // 收集所有单元格的静态值（不计算公式）
            Map<String, Object> allCellValues = collectAllStaticCellValues(workbook);

            log.info("开始计算 {} 个公式", formulaMap.size());

            for (Map.Entry<String, String> entry : formulaMap.entrySet()) {
                String cellAddress = entry.getKey();
                String formula = entry.getValue();

                try {
                    Object result = calculateSingleFormula(formula, allCellValues);
                    // 统一格式化为两位小数
                    Object formattedResult = formatToTwoDecimalPlaces(result);
                    results.put(cellAddress, formattedResult);

                    log.debug("公式计算完成 - 单元格: {}, 公式: {}, 结果: {}", cellAddress, formula, formattedResult);

                } catch (Exception e) {
                    log.error("公式计算失败 - 单元格: {}, 公式: {}", cellAddress, formula, e);
                    // 计算失败时返回默认值
                    Object fallbackResult = getDefaultValueFromFormula(formula);
                    Object formattedFallbackResult = formatToTwoDecimalPlaces(fallbackResult);
                    results.put(cellAddress, formattedFallbackResult);
                }
            }

            log.info("公式计算完成，成功计算 {} 个公式", results.size());

        } catch (Exception e) {
            log.error("批量计算公式时发生严重错误", e);
        }

        return results;
    }

    /**
     * 计算单个公式
     */
    private Object calculateSingleFormula(String formula, Map<String, Object> cellValues) {
        try {
            if (formula == null || formula.trim().isEmpty()) {
                return 0.0;
            }

            // 清理公式，去掉等号
            String cleanFormula = formula.trim();
            if (cleanFormula.startsWith("=")) {
                cleanFormula = cleanFormula.substring(1);
            }

            // 检查是否包含 Excel 函数
            if (containsExcelFunction(cleanFormula)) {
                return handleExcelFunctionFormula(cleanFormula, cellValues);
            }

            // 处理简单算术公式
            return handleArithmeticFormula(cleanFormula, cellValues);

        } catch (Exception e) {
            log.warn("公式计算失败: {}, 错误: {}", formula, e.getMessage());
            return 0.0;
        }
    }

    /**
     * 检查是否包含 Excel 函数
     */
    private boolean containsExcelFunction(String formula) {
        if (formula == null || formula.trim().isEmpty()) {
            return false;
        }

        String upperFormula = formula.toUpperCase();
        for (String function : EXCEL_FUNCTIONS) {
            if (upperFormula.contains(function + "(")) {
                return true;
            }
        }
        return false;
    }

    /**
     * 处理 Excel 函数公式
     */
    private Object handleExcelFunctionFormula(String formula, Map<String, Object> cellValues) {
        if (formula == null || formula.trim().isEmpty()) {
            return 0.0;
        }

        String upperFormula = formula.toUpperCase();

        // 处理 IF(ISERROR(...)) 模式
        if (upperFormula.startsWith("IF(ISERROR(")) {
            return handleIsErrorFormula(formula, cellValues);
        }

        // 处理 IF 函数
        if (upperFormula.startsWith("IF(")) {
            return handleIfFormula(formula, cellValues);
        }

        // 处理 ISERROR 函数
        if (upperFormula.startsWith("ISERROR(")) {
            return handleIsErrorFunction(formula, cellValues);
        }

        // 处理其他函数或默认情况
        log.warn("未支持的 Excel 函数公式: {}", formula);
        return getDefaultValueFromFormula(formula);
    }

    /**
     * 处理 IF 公式
     */
    private Object handleIfFormula(String formula, Map<String, Object> cellValues) {
        try {
            // 提取 IF 函数的参数
            String[] ifParams = extractIfParameters(formula);
            if (ifParams == null || ifParams.length < 3) {
                log.warn("IF 公式参数不足或解析失败: {}", formula);
                return getDefaultValueFromFormula(formula);
            }

            String condition = ifParams[0];
            String trueValue = ifParams[1];
            String falseValue = ifParams[2];

            // 计算条件表达式
            boolean conditionResult = evaluateCondition(condition, cellValues);

            // 根据条件结果返回相应的值
            String resultExpression = conditionResult ? trueValue : falseValue;
            return calculateSingleFormula(resultExpression, cellValues);

        } catch (Exception e) {
            log.warn("IF 公式计算失败: {}, 错误: {}", formula, e.getMessage());
            return getDefaultValueFromFormula(formula);
        }
    }

    /**
     * 提取 IF 函数的参数 - 修复空指针问题
     */
    private String[] extractIfParameters(String ifFormula) {
        if (ifFormula == null || ifFormula.length() <= 3) {
            return new String[0];
        }

        try {
            // 移除 IF 关键字和括号
            String content = ifFormula.substring(2).trim(); // 移除 "IF"
            if (content.startsWith("(") && content.endsWith(")")) {
                content = content.substring(1, content.length() - 1); // 移除外层括号
            }

            List<String> params = new ArrayList<>();
            int bracketCount = 0;
            StringBuilder currentParam = new StringBuilder();

            for (char c : content.toCharArray()) {
                if (c == '(') {
                    bracketCount++;
                } else if (c == ')') {
                    bracketCount--;
                }

                if (c == ',' && bracketCount == 0) {
                    String param = currentParam.toString().trim();
                    if (!param.isEmpty()) {
                        params.add(param);
                    }
                    currentParam = new StringBuilder();
                } else {
                    currentParam.append(c);
                }
            }

            // 添加最后一个参数
            String lastParam = currentParam.toString().trim();
            if (!lastParam.isEmpty()) {
                params.add(lastParam);
            }

            if (params.size() < 3) {
                log.warn("IF 公式参数数量不足: {}, 提取到的参数: {}", ifFormula, params);
                return new String[0];
            }

            return params.toArray(new String[0]);

        } catch (Exception e) {
            log.error("提取 IF 参数失败: {}, 错误: {}", ifFormula, e.getMessage());
            return new String[0];
        }
    }

    /**
     * 计算条件表达式
     */
    private boolean evaluateCondition(String condition, Map<String, Object> cellValues) {
        if (condition == null || condition.trim().isEmpty()) {
            return false;
        }

        try {
            String trimmedCondition = condition.trim();

            // 处理常见的比较操作符
            if (trimmedCondition.contains(">=")) {
                String[] parts = splitCondition(trimmedCondition, ">=");
                if (parts.length == 2) {
                    Object left = calculateSingleFormula(parts[0], cellValues);
                    Object right = calculateSingleFormula(parts[1], cellValues);
                    return toDouble(left) >= toDouble(right);
                }
            } else if (trimmedCondition.contains("<=")) {
                String[] parts = splitCondition(trimmedCondition, "<=");
                if (parts.length == 2) {
                    Object left = calculateSingleFormula(parts[0], cellValues);
                    Object right = calculateSingleFormula(parts[1], cellValues);
                    return toDouble(left) <= toDouble(right);
                }
            } else if (trimmedCondition.contains("<>") || trimmedCondition.contains("!=")) {
                String[] parts = splitCondition(trimmedCondition, "<>", "!=");
                if (parts.length == 2) {
                    Object left = calculateSingleFormula(parts[0], cellValues);
                    Object right = calculateSingleFormula(parts[1], cellValues);
                    return Math.abs(toDouble(left) - toDouble(right)) > 0.0001;
                }
            } else if (trimmedCondition.contains(">")) {
                String[] parts = splitCondition(trimmedCondition, ">");
                if (parts.length == 2) {
                    Object left = calculateSingleFormula(parts[0], cellValues);
                    Object right = calculateSingleFormula(parts[1], cellValues);
                    return toDouble(left) > toDouble(right);
                }
            } else if (trimmedCondition.contains("<")) {
                String[] parts = splitCondition(trimmedCondition, "<");
                if (parts.length == 2) {
                    Object left = calculateSingleFormula(parts[0], cellValues);
                    Object right = calculateSingleFormula(parts[1], cellValues);
                    return toDouble(left) < toDouble(right);
                }
            } else if (trimmedCondition.contains("=")) {
                String[] parts = splitCondition(trimmedCondition, "=");
                if (parts.length == 2) {
                    Object left = calculateSingleFormula(parts[0], cellValues);
                    Object right = calculateSingleFormula(parts[1], cellValues);
                    return Math.abs(toDouble(left) - toDouble(right)) < 0.0001; // 浮点数比较容差
                }
            }

            // 默认情况下，将非零值视为 true
            Object result = calculateSingleFormula(trimmedCondition, cellValues);
            return toDouble(result) != 0.0;

        } catch (Exception e) {
            log.warn("条件表达式计算失败: {}, 错误: {}", condition, e.getMessage());
            return false;
        }
    }

    /**
     * 分割条件表达式
     */
    private String[] splitCondition(String condition, String... operators) {
        if (condition == null) {
            return new String[0];
        }

        for (String operator : operators) {
            if (condition.contains(operator)) {
                int index = condition.indexOf(operator);
                String left = condition.substring(0, index).trim();
                String right = condition.substring(index + operator.length()).trim();
                return new String[]{left, right};
            }
        }
        return new String[]{condition};
    }

    /**
     * 转换为 double
     */
    private double toDouble(Object value) {
        if (value instanceof Number) {
            return ((Number) value).doubleValue();
        } else if (value instanceof String) {
            try {
                return Double.parseDouble((String) value);
            } catch (NumberFormatException e) {
                return 0.0;
            }
        }
        return 0.0;
    }

    /**
     * 处理 ISERROR 函数
     */
    private Object handleIsErrorFunction(String formula, Map<String, Object> cellValues) {
        try {
            // 提取 ISERROR 的参数
            String expression = extractFunctionParameter(formula, "ISERROR");
            Object result = calculateSingleFormula(expression, cellValues);
            return isErrorValue(result) ? true : false;
        } catch (Exception e) {
            log.warn("ISERROR 函数计算失败: {}", formula);
            return true; // 如果计算失败，认为是错误
        }
    }

    /**
     * 处理 IF(ISERROR(...)) 公式
     */
    private Object handleIsErrorFormula(String formula, Map<String, Object> cellValues) {
        try {
            // 提取 ISERROR 检查的表达式
            String expression = extractExpressionFromIsError(formula);

            // 尝试计算表达式，如果出错则返回默认值
            try {
                Object result = calculateSingleFormula(expression, cellValues);
                if (isErrorValue(result)) {
                    return getDefaultValueFromFormula(formula);
                }
                return result;
            } catch (Exception e) {
                return getDefaultValueFromFormula(formula);
            }

        } catch (Exception e) {
            log.warn("ISERROR 公式计算失败: {}, 使用默认值", formula);
            return getDefaultValueFromFormula(formula);
        }
    }

    /**
     * 提取函数参数
     */
    private String extractFunctionParameter(String formula, String functionName) {
        if (formula == null || functionName == null) {
            return "0";
        }

        Pattern pattern = Pattern.compile(functionName + "\\s*\\(([^)]+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(formula);
        if (matcher.find()) {
            return matcher.group(1).trim();
        }
        return "0";
    }

    /**
     * 检查是否为错误值
     */
    private boolean isErrorValue(Object value) {
        if (value == null) {
            return true;
        }
        if (value instanceof Double) {
            double doubleValue = (Double) value;
            return Double.isNaN(doubleValue) || Double.isInfinite(doubleValue);
        }
        return false;
    }

    /**
     * 从公式中提取默认值
     */
    private Object getDefaultValueFromFormula(String formula) {
        if (formula == null) {
            return 0.0;
        }

        if (formula.contains("\"0.00\"")) {
            return 0.0;
        }

        // 尝试提取 IF(ISERROR(...), 默认值, ...) 中的默认值
        Pattern pattern = Pattern.compile("IF\\s*\\(\\s*ISERROR\\s*\\([^)]+\\)\\s*,\\s*([^,]+)\\s*,", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(formula);
        if (matcher.find()) {
            String defaultValue = matcher.group(1).trim();
            // 去掉可能的引号
            defaultValue = defaultValue.replace("\"", "");
            try {
                if (defaultValue.matches("-?\\d+\\.?\\d*")) {
                    return Double.parseDouble(defaultValue);
                }
            } catch (NumberFormatException e) {
                log.debug("默认值 {} 不是有效数字", defaultValue);
            }
        }
        return 0.0;
    }

    /**
     * 处理算术公式
     */
    private Object handleArithmeticFormula(String formula, Map<String, Object> cellValues) {
        try {
            return calculateArithmeticExpression(formula, cellValues);
        } catch (Exception e) {
            log.warn("算术公式计算失败: {}, 错误: {}", formula, e.getMessage());
            return 0.0;
        }
    }

    /**
     * 计算算术表达式
     */
    private Object calculateArithmeticExpression(String expression, Map<String, Object> cellValues) {
        if (expression == null || expression.trim().isEmpty()) {
            return 0.0;
        }

        // 先替换单元格引用
        String processedExpression = replaceCellReferences(expression, cellValues);

        // 清理表达式，移除可能的多余空格
        processedExpression = processedExpression.replaceAll("\\s+", "");

        log.debug("计算表达式: {} -> {}", expression, processedExpression);

        // 检查表达式是否为空或只包含运算符
        if (processedExpression.isEmpty() ||
                processedExpression.matches("[+\\-*/]+") ||
                processedExpression.equals("0/0") ||
                processedExpression.equals("0.0/0.0")) {
            return 0.0;
        }

        Object result = evaluateExpression(processedExpression);
        // 计算结果也格式化为两位小数
        return formatToTwoDecimalPlaces(result);
    }

    /**
     * 替换单元格引用
     */
    private String replaceCellReferences(String expression, Map<String, Object> cellValues) {
        if (expression == null || cellValues == null) {
            return "0";
        }

        Pattern pattern = Pattern.compile("[A-Z]+\\d+");
        Matcher matcher = pattern.matcher(expression);

        StringBuffer result = new StringBuffer();
        while (matcher.find()) {
            String cellRef = matcher.group();
            Object value = cellValues.get(cellRef);

            if (value == null) {
                log.debug("单元格引用 {} 未找到值，使用默认值 0", cellRef);
                matcher.appendReplacement(result, "0");
            } else {
                String valueStr = value.toString();
                try {
                    // 验证是否为有效数字
                    Double.parseDouble(valueStr);
                    matcher.appendReplacement(result, valueStr);
                } catch (NumberFormatException e) {
                    log.debug("单元格引用 {} 的值 {} 不是有效数字，使用 0", cellRef, valueStr);
                    matcher.appendReplacement(result, "0");
                }
            }
        }
        matcher.appendTail(result);
        return result.toString();
    }

    /**
     * 计算表达式
     */
    private Object evaluateExpression(String expression) {
        if (expression == null || expression.trim().isEmpty()) {
            return 0.0;
        }

        try {
            // 处理除零情况
            if (expression.contains("/0") && !expression.contains("/0.")) {
                String[] parts = expression.split("/");
                if (parts.length == 2) {
                    String denominator = parts[1].trim();
                    if (denominator.equals("0") || denominator.matches("^0[^.]*$")) {
                        return 0.0; // 除零返回0
                    }
                }
            }

            ScriptEngineManager manager = new ScriptEngineManager();
            ScriptEngine engine = manager.getEngineByName("JavaScript");
            Object result = engine.eval(expression);

            if (result instanceof Number) {
                double doubleValue = ((Number) result).doubleValue();
                if (Double.isNaN(doubleValue) || Double.isInfinite(doubleValue)) {
                    return 0.0;
                }
                return doubleValue;
            }
            return 0.0;

        } catch (ScriptException e) {
            log.warn("JavaScript 表达式计算失败: {}, 错误: {}", expression, e.getMessage());
            return 0.0;
        } catch (Exception e) {
            log.warn("表达式计算发生未知错误: {}, 错误: {}", expression, e.getMessage());
            return 0.0;
        }
    }

    /**
     * 从 ISERROR 公式中提取表达式
     */
    private String extractExpressionFromIsError(String formula) {
        if (formula == null) {
            return "0";
        }

        // 匹配 IF(ISERROR(expression), default, expression) 模式
        Pattern pattern = Pattern.compile("IF\\s*\\(\\s*ISERROR\\s*\\(([^)]+)\\)\\s*,\\s*[^,]+\\s*,\\s*([^)]+)\\)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(formula);

        if (matcher.find()) {
            // 返回原始表达式（第二个捕获组）
            return matcher.group(2);
        }

        // 如果无法提取，返回原公式
        return formula;
    }

    /**
     * 统一格式化为两位小数
     */
    private Object formatToTwoDecimalPlaces(Object value) {
        if (value instanceof Double) {
            double doubleValue = (Double) value;
            // 使用 BigDecimal 确保精度
            return BigDecimal.valueOf(doubleValue)
                    .setScale(2, RoundingMode.HALF_UP)
                    .doubleValue();
        } else if (value instanceof Integer) {
            return BigDecimal.valueOf((Integer) value)
                    .setScale(2, RoundingMode.HALF_UP)
                    .doubleValue();
        } else if (value instanceof Long) {
            return BigDecimal.valueOf((Long) value)
                    .setScale(2, RoundingMode.HALF_UP)
                    .doubleValue();
        } else if (value instanceof Float) {
            return BigDecimal.valueOf((Float) value)
                    .setScale(2, RoundingMode.HALF_UP)
                    .doubleValue();
        } else if (value instanceof BigDecimal) {
            return ((BigDecimal) value).setScale(2, RoundingMode.HALF_UP).doubleValue();
        }
        // 如果不是数字类型，直接返回原值
        return value;
    }

    /**
     * 收集工作簿中所有单元格的静态值（不计算公式）
     */
    private Map<String, Object> collectAllStaticCellValues(Workbook workbook) {
        Map<String, Object> cellValues = new HashMap<>();

        if (workbook == null) {
            log.warn("工作簿为空，无法收集单元格值");
            return cellValues;
        }

        try {
            for (Sheet sheet : workbook) {
                if (sheet == null) continue;

                for (Row row : sheet) {
                    if (row == null) continue;

                    for (Cell cell : row) {
                        if (cell == null) continue;

                        try {
                            String cellAddress = getCellAddress(cell);
                            Object value = getStaticCellValue(cell);
                            cellValues.put(cellAddress, value);

                            // 如果是公式单元格，也记录公式
                            if (cell.getCellType() == CellType.FORMULA) {
                                cellValues.put(cellAddress + "_FORMULA", cell.getCellFormula());
                            }
                        } catch (Exception e) {
                            log.warn("处理单元格时发生错误，跳过该单元格", e);
                        }
                    }
                }
            }

            log.info("收集到 {} 个单元格的静态值", cellValues.size());

        } catch (Exception e) {
            log.error("收集单元格静态值时发生严重错误", e);
        }

        return cellValues;
    }

    /**
     * 获取单元格静态值（不计算公式）
     */
    private Object getStaticCellValue(Cell cell) {
        try {
            if (cell == null) {
                return 0.0;
            }

            switch (cell.getCellType()) {
                case NUMERIC:
                    return cell.getNumericCellValue();
                case STRING:
                    return cell.getStringCellValue();
                case BOOLEAN:
                    return cell.getBooleanCellValue();
                case FORMULA:
                    // 对于公式单元格，我们暂时返回0，后续会专门计算
                    // 这样可以避免循环依赖
                    return 0.0;
                case BLANK:
                    return 0.0;
                default:
                    return 0.0;
            }
        } catch (Exception e) {
            log.warn("获取单元格值时发生错误: {}", e.getMessage());
            return 0.0;
        }
    }

    /**
     * 获取单元格地址
     */
    private String getCellAddress(Cell cell) {
        if (cell == null) {
            return "A1";
        }
        return cell.getAddress().formatAsString();
    }
}
